BEGIN;

--上下班记录
CREATE TABLE IF NOT EXISTS duty.dutyrecords
(
signDate DATE NOT NULL,
uid VARCHAR(100) NOT NULL,
signInTime TIME,
signOutTime TIME,
workTime TIME,
overTime TIME,
late boolean,
leaveEarly boolean,
PRIMARY KEY (signDate,uid)
);

--忘打卡申请
CREATE TABLE IF NOT EXISTS duty.abnormalsignapply
(
id BIGINT AUTO_INCREMENT NOT NULL,
uid VARCHAR(100) NOT NULL,
applyTime TIMESTAMP NOT NULL,
signType INTEGER NOT NULL,
status SMALLINT NOT NULL,--0-draft,1-processing,2-approved,3-rejected
abnormalDate DATE NOT NULL,
actualSignTime TIME NOT NULL,
reason VARCHAR(500),
lastUpdateTime TIMESTAMP,
approver VARCHAR(100),--审核人
approveTime TIMESTAMP,
suggestion VARCHAR(500),
PRIMARY KEY (id)
);

INSERT INTO duty.dutyrecords
(signDate,uid,signInTime,signOutTime)
SELECT m.signDate,m.uid,
		MIN(m.signTime) AS signInTime,
		CASEWHEN(COUNT(m.signTime)>1,MAX(m.signTime),NULL) AS signOutTime
FROM DUTY.signrecords m
GROUP BY signDate,uid;

--请假/外出申请
CREATE TABLE IF NOT EXISTS duty.leaveapply
(
id BIGINT AUTO_INCREMENT NOT NULL,
uid VARCHAR(100) NOT NULL,
applyTime TIMESTAMP NOT NULL,
leaveType INTEGER NOT NULL,
fromDate DATE NOT NULL,
toDate DATE NOT NULL,
fromTime TIME NOT NULL,
toTime TIME NOT NULL,
status INTEGER NOT NULL,--0-draft,1-processing,2-approved,3-rejected
reason VARCHAR(500),
lastUpdateTime TIMESTAMP,
approver VARCHAR(100),
approveTime TIMESTAMP,
suggestion VARCHAR(500),
PRIMARY KEY (id)
);


CREATE SCHEMA IF NOT EXISTS conf;
CREATE TABLE IF NOT EXISTS conf.settings
(
NAME VARCHAR(100) NOT NULL,
VAL VARCHAR(100) NOT NULL,
sortNo BIGINT,
PRIMARY KEY (NAME)
);

INSERT INTO conf.settings
(name,val,sortNo)
VALUES
('attendance.clerk','00002',null),
('leave.types.vacation','请假',1),
('leave.types.business','外出公干',2),
('leave.types.overtime','加班',4),
('sign.types.in','上班',1),
('sign.types.out','下班',2),
('apply.status.draft','草稿',0),
('apply.status.processing','待审核',1),
('apply.status.approved','通过',2),
('apply.status.rejected','驳回',3),
('approve.status.approved','通过',2),
('approve.status.rejected','驳回',3),
('users.roles.hr','hr',1),
('users.roles.supervisor','supervisor',2),
;

COMMIT;